﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CommClass
{
    class Surplier
    {
        private DBSource ds;
        public Surplier(DBSource dbSource)
        {
            this.ds = dbSource;
        }

        //获取供应商状态
        public DataTable GetSurplyStatus()  
        {
            DataSet dt = new DataSet();
            string sqlstr = "select SupplySupplierStatusID,SupplySupplierStatusmiaoshu from t_SupplySupplierStatus order by SupplySupplierStatusID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取商品性质
        public DataTable GetShangpinshuxing()  
        {
            DataSet dt = new DataSet();
            string sqlstr = "select shangpinshuxingID,shangpinshuxingmiaoshu from t_shangpinshuxing order by shangpinshuxingID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取发票
        public DataTable Getfapiao()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select fapiaoID,fapiaomiaoshu from t_fapiao order by fapiaoID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取收货属性
        public DataTable Getshouhuoshuxing()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select shouhuoshuxingID,shouhuoshuxinmiaoshu from t_shouhuoshuxing order by shouhuoshuxingID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取层级
        public DataTable Getcengji()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select cengjiID,cengjimiaoshu from t_cengji order by cengjiID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取付款方式
        public DataTable Getfukuan()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select fukuanID,fukuanmiaoshu from t_fukuan order by fukuanID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //根据筛选条件查询供应商数据分页
        public DataTable getSurplierData(int pageSize, int currentPage, string strWhere, string filedOrder)
        {  
            DataSet dt = new DataSet();
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top  " + pageSize + " a.[SupplySupplierName] 供应商名称,b.StorageName  越库库位,c.shouhuoshuxinmiaoshu 收货属性,d.cengjimiaoshu 层级,e.SupplySupplierStatusmiaoshu  状态,a.[SupplySupplierID] ");
            strSql.Append("    FROM  [t_SupplySupplier] a left join t_Storage b on  a.[StorageID] = b.[StorageID] left join t_shouhuoshuxing c on a.[shouhuoshuxingID]=c.[shouhuoshuxingID] left join t_cengji d on a.[cengjiID] = d.[cengjiID] left join  t_SupplySupplierStatus e  on  a.[SupplySupplierStatusID] = e.[SupplySupplierStatusID]  ");
            strSql.Append("  where   ");
            strSql.Append("  a.[SupplySupplierID] not in(select top " + topSize + " a.[SupplySupplierID] from [t_SupplySupplier] a  ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }


        //获取供应商分页信息
        public int getSurpliercount(string strWhere) 
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)     from [t_SupplySupplier] a  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where" + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }


        //根据筛选条件查询供应商数据
        public DataTable getSurplierDataBy( string strWhere )
        {
            DataSet dt = new DataSet();
            
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT a.[SupplySupplierID],a.[SupplySupplierName],a.[ShortName],a.[StorageID],b.StorageName,a.[shouhuoshuxingID],a.[cengjiID],a.[dizhi] ");
            strSql.Append("        ,a.[hezuoshangpin],a.[lianxifangshi],a.[yinhangzhanghao],a.[gonghuofanwei],a.[qisongliang],a.[tiqianqi],a.[fukuanID],a.[zhangqi],a.[SupplySupplierStatusID]  ");
            strSql.Append("  FROM  [t_SupplySupplier] a  left join t_Storage b on  a.[StorageID] = b.[StorageID]   ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //根据筛选条件查询供应商数据查看
        public DataTable getSurplierDataBycheck(string strWhere)  
        {
            DataSet dt = new DataSet();

            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT a.[SupplySupplierID],a.[SupplySupplierName],a.[ShortName],a.[StorageID],b.StorageName,a.[shouhuoshuxingID],c.shouhuoshuxinmiaoshu,a.[cengjiID],d.cengjimiaoshu,a.[dizhi]   ");
            strSql.Append("       ,a.[hezuoshangpin],a.[lianxifangshi],a.[yinhangzhanghao],a.[gonghuofanwei],a.[qisongliang],a.[tiqianqi],a.[fukuanID],e.fukuanmiaoshu,a.[zhangqi],a.[SupplySupplierStatusID],f.SupplySupplierStatusmiaoshu  ");
            strSql.Append("   FROM  [t_SupplySupplier] a  left join t_Storage b on  a.[StorageID] = b.[StorageID] left join t_shouhuoshuxing c on a.shouhuoshuxingID = c.shouhuoshuxingID  left join t_cengji d on a.cengjiID = d.cengjiID  left join t_fukuan e on a.fukuanID = e.fukuanID left join t_SupplySupplierStatus f on a.SupplySupplierStatusID = f.SupplySupplierStatusID  ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //根据筛选条件查询供应商商品对应信息
        public DataTable getSurplierproductDataBy(string strWhere)  
        {
            DataSet dt = new DataSet();

            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT a.[SupplySupplierProductID],a.[SupplySupplierID],a.[ProdID],b.[ProdCode],b.[ProdName],a.[shangpinshuxingID],a.[youxianji],a.[caigoujia] ,a.[wuliufei],a.[qitafeiyong],a.[fapiaoID],a.[fandian] ");
            
            strSql.Append("  FROM  [t_SupplySupplierProduct] a ,[t_Product] b where  a.[ProdID] = b.[ProdID]   ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }

            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }




        //根据筛选条件查询供应商商品对应信息查看
        public DataTable getSurplierproductDataBycheck(string strWhere)
        {
            DataSet dt = new DataSet();

            StringBuilder strSql = new StringBuilder();

            strSql.Append("  SELECT b.[ProdCode] 商品编号,b.[ProdName] 商品名称,c.[shangpinshuxingmiaoshu] 性质,a.[youxianji] 优先级 ,a.[caigoujia] 采购价 ,a.[wuliufei] 物流费,a.[qitafeiyong] 其他费用,d.[fapiaomiaoshu] 发票,a.[fandian] 返点   ");

            strSql.Append("   FROM  [t_SupplySupplierProduct] a ,[t_Product] b ,t_shangpinshuxing c,t_fapiao d  where  a.[ProdID] = b.[ProdID] and a.[shangpinshuxingID] = c.[shangpinshuxingID] and a.[fapiaoID] = d.[fapiaoID]  ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }

            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }



        /// <summary>
        /// 取得货位ID 
        /// </summary>
        /// <returns>ID值</returns>
        public  int GetStorageID(string StorageName, SqlConnection connection, SqlCommand cmd) 
        {
            string sqlCmd;
            sqlCmd = "select StorageID from t_Storage where StorageName='" + StorageName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }



        /// <summary>
        /// 取得属性ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetshouhuoshuxingID(string shouhuoshuxinmiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select shouhuoshuxingID from t_shouhuoshuxing where shouhuoshuxinmiaoshu='" + shouhuoshuxinmiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 取得层级ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetcengjiID(string cengjimiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select cengjiID from t_cengji where cengjimiaoshu='" + cengjimiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 取得付款ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetfukuanID(string fukuanmiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select fukuanID from t_fukuan where fukuanmiaoshu='" + fukuanmiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }



        /// <summary>
        /// 取得状态ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetSupplySupplierStatusID(string SupplySupplierStatusmiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select SupplySupplierStatusID from t_SupplySupplierStatus where SupplySupplierStatusmiaoshu='" + SupplySupplierStatusmiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 取得供应商ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetSupplySupplierID(string SupplySupplierName, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select SupplySupplierID from t_SupplySupplier where SupplySupplierName='" + SupplySupplierName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 取得商品ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetProdID(string ProdCode, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select ProdID from t_Product where ProdCode='" + ProdCode.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }



        /// <summary>
        /// 取得商品属性ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetshangpinshuxingID(string shangpinshuxingmiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select shangpinshuxingID from t_shangpinshuxing where shangpinshuxingmiaoshu='" + shangpinshuxingmiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }



        /// <summary>
        /// 取得发票ID 
        /// </summary>
        /// <returns>ID值</returns>
        public int GetfapiaoID(string fapiaomiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select fapiaoID from t_fapiao where fapiaomiaoshu='" + fapiaomiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }



        /// <summary>
        /// 查询该供应商的该商品是否存在
        /// </summary>
        /// <returns>ID值</returns>
        public int GetSupplySupplierProductID(string SupplySupplierID,string ProdID, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select SupplySupplierProductID from t_SupplySupplierProduct where SupplySupplierID='" + SupplySupplierID.Trim() + "' and ProdID = '" + ProdID.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

    }


 }

    



